﻿CREATE PROCEDURE [dbo].[proc_Statistics_Performance_Create]
AS
BEGIN
	SET NOCOUNT ON;
	Declare @date varchar(10)=convert(varchar(10),getdate(),120)
	Declare @sRq datetime = @date+' 00:00:00'
	Declare @eRq datetime = @date+' 23:59:59'
	Declare @companyId int
	Declare @companyName nvarchar(50)

	Declare cur_company cursor  
	local For SELECT Id,CompanyName from Company 
		where Id in (1,18,20,22,26,33,34,35,36,50,51,52,54,55,57,65,66,68,69,70,77,84,91,92,93,96,101,102) --龙采
			Or Id in (16,21,24,30,31,63,71,88,94,107,114,115,130,133,134,147) --资海

	open cur_company
	fetch next from cur_company into @companyId,@companyName

	While(@@Fetch_Status = 0)
	Begin
		Declare @IAA decimal(18,2),@IAB decimal(18,2),@IAC decimal(18,2),@fyze decimal(18,2),@yggz decimal(18,2),@sf decimal(18,2),@ff decimal(18,2)
		Declare @zbfy decimal(18,2),@ygfl decimal(18,2),@sjglf decimal(18,2),@gdzc decimal(18,2),@zxf decimal(18,2),@qtzc decimal(18,2),@rcfy decimal(18,2),@fqzc decimal(18,2),@qqqd decimal(18,2) 
		Declare @sscl decimal(18,2),@cl decimal(18,2),@IAF decimal(18,2),@jkzc decimal(18,2),@jksr decimal(18,2),@fyyxsr decimal(18,2),@jjbcfk decimal(18,2)
		--新增毛利AND Project.NOE_Flag<>2|and oid IN (SELECT oid FROM [Order] o WHERE companyId=@companyId AND o.NOE_Flag<>2 )
		Set @IAA=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=0 ),0)+
			ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=0 ),0)
		--续费毛利	
		Set @IAB=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=0 ),0)+
				ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=0),0)
		--2015-03-09 新增应收按财务认领时间统计 + 2016-03-05 包含非常规绿色通道
		Set @IAC=ISNULL((Select Sum(h_yingshou) From [Order] WHERE CompanyId=@CompanyId  AND StateId>=1  and financerenlingdate>=@sRq and financerenlingdate<=@eRq AND NOE_Flag<>2 ),0)+
		ISNULL((SELECT sum(dch_ysh) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid  and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)+
		ISNULL((Select Sum(h_yingshou) From [Order] WHERE CompanyId=@CompanyId and financerenlingdate>=@sRq and financerenlingdate<=@eRq AND StateId>=1  AND NOE_Flag=2 And IsGreen=1),0)
		--尾款收入
		Set @IAF=ISNULL((Select Sum(Prices) From Order_PaymentRecord inner JOIN [Order] o on  Order_PaymentRecord.oid = o.oid and Order_PaymentRecord.CompanyId=@companyId and FinanceDate>=@sRq and FinanceDate<=@eRq and SWId=2 and  o.companyId=@companyId AND o.NOE_Flag<>2),0)+
			ISNULL((SELECT sum(dch_wk) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq and o.companyId=@companyId AND o.NOE_Flag<>2 ),0)

		--员工工资sum([OutlayPrice])
		set @yggz=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=211 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=211  ),0)
		--税费
		set @sf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=213 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=213  ),0)
		 --日常费用
		set @rcfy= isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] in (  217,218,215,219,220,221,222,223,224,225,226,228,240,241,230,242,232,233,175) and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid in (  217,218,215,219,220,221,222,223,224,225,226,228,240,241,230,242,232,233,175)  ),0)
		--房费
		set @ff=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=214 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=214  ),0)
		--总办费用
		set @zbfy=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=239 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=239  ),0)
		--员工福利
		set @ygfl=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=235 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=235  ),0)
		--上缴管理费
		set @sjglf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=207 and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=207  ),0)
		--固定资产及无形资产
		set @gdzc=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] in (176,174) and createdate between @sRq and @eRq),0)
		+ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid in (176,174)  ),0)
		--装修费
		 set @zxf=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId]=231 and createdate between @sRq and @eRq),0)
		 +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=231  ),0)
		--其他支出
		 set @qtzc=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] =197 and createdate between @sRq and @eRq),0)
		  +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=197  ),0)
		--QQ渠道
		set @qqqd=isnull((select sum([OutlayPrice]) from Finance_Outlay where companyidcur=@companyId and [SubjectId] =246 and createdate between @sRq and @eRq),0)
		  +ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=197  ),0)
		--费用总额
		--2016-03-05 增加非企支出
		Set @fqzc=ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=1 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+ 
		ISNULL((SELECT sum(dch_xzml) FROM Order_Change_Hedge inner JOIN [Order] o on Order_Change_Hedge.oid = o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)+
		ISNULL((Select Sum(SalePrice-CostPrice) From Project Where CompanyId=@companyId and  CreateDate>=@sRq and CreateDate<=@eRq  and TypeId=2 AND ProductId not IN (22,29,33) and SalePrice<>CostPrice  AND Project.NOE_Flag=1),0)+
		ISNULL((SELECT sum(dch_xfml) FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>=@sRq and UpTime<=@eRq  and o.companyId=@companyId AND o.NOE_Flag=1),0)
		--借款支出
		set @jkzc=isnull((select sum(OutlayPrice) from Finance_Outlay where companyidcur=@companyId and createdate between  @sRq and @eRq
		AND Subjectid=203),0)+
					ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=1  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and Subjectid=203  ),0)
		--借款收入
		set @jksr=isnull((select sum(income) from Finance_RunningAccount where companyid=@companyId and 
		createdate between  @sRq and @eRq  and charindex('[借款收入]',Summary,0)>0),0)+
				ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=1  ),0)
		--非营业性收入
		set @fyyxsr=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=4 and createdate between  @sRq and @eRq),0)+
				ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=4  ),0)
		--奖金补偿返款
		set @jjbcfk=isnull((select sum(income) from Finance_RunningAccount_Non where companyid=@companyId and AccountsTypeId=6 and createdate between  @sRq and @eRq),0)+
		ISNULL((Select Sum([money]) From FinanceMonthTable_PZ Where CompanyId=@companyId and TypeId=2  
		and  CreateDate>=@sRq and CreateDate<=@eRq  and AccountType=6  ),0)

		set @fyze=@yggz+@sf+@ff+@zbfy+@ygfl+@sjglf+@gdzc+@zxf+@qtzc+@rcfy+abs(@fqzc)+@qqqd
		SET @cl=((@IAA+@IAB)-@fyze)
		SET @sscl=(@cl-@IAC+@IAF-@jkzc+@jksr+@fyyxsr+@jjbcfk)
		
		/************************↓成单数统计↓********************************
		2015-01-22 统计单数，用于月报统计
		*/
		Declare @bd_je1 DECIMAL(18,2),@qyQQ_je1 DECIMAL(18,2),@bd_je  DECIMAL(18,2),@wz_je DECIMAL(18,2)
		Declare @qyQQ_je DECIMAL(18,2), @qt_je DECIMAL(18,2),@idc_je DECIMAL(18,2),@bdztc_je decimal(18,2)
		Declare @bd int,@wz int,@qyQQ int,@idc int,@qt int,@bdztc int

		set @bd_je1=0
		set @qyQQ_je1=0
		--百度单数
		set @bd=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='bd') And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='bd') And IsHedge=-1),0))) 
		--百度金额
		set @bd_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='bd') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='bd') And IsHedge=-1),0))) 
		--百度服务费
		set @bd_je1=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='bdxse') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='bdxse') And IsHedge=-1),0))) 
		--百度服务费不算成单数，只在统计销售额时参与计算
		set @bd_je=@bd_je+@bd_je1

		--网站单数  in (select id from Product where flag=1 ) 
		set @wz=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='wz') And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='wz' ) And IsHedge=-1),0))) 
		--网站金额
		set @wz_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='wz') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='wz') And IsHedge=-1),0)))
		----企业QQ单数68,71,112,116
		set @qyQQ=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='qyqq') And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='qyqq') And IsHedge=-1),0))) 
		--企业QQ金额
		set @qyQQ_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqq') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqq') And IsHedge=-1),0))) 

		set @qyQQ_je1=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqqxse') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qyqqxse') And IsHedge=-1),0))) 
		--企业QQ服务费不算成单数，只在统计销售额时参与计算
		set @qyQQ_je=@qyQQ_je+@qyQQ_je1

		--其他产品单数
		set @qt=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='qt') And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='qt') And IsHedge=-1),0))) 
		--其他产品金额
		set @qt_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qt') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='qt') And IsHedge=-1),0))) 
		--IDC产品单数
		set @idc=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='idc') And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId in (select id from product where f_flag='idc') And IsHedge=-1),0))) 
		--IDC产品金额
		set @idc_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='idc') And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (select id from product where f_flag='idc') And IsHedge=-1),0))) 

		--百度直通车单数
		set @bdztc=(isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId =257 And (IsHedge=0 Or IsHedge=-2)),0) 
		- (isnull((Select Count(Distinct OrderId) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1 And SalePrice!=0 And ProductId =257 And IsHedge=-1),0))) 
		--百度直通车金额
		set @bdztc_je=(isnull((Select sum( saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq And CompanyId =@companyId 
		And NOE_Flag=0 And TypeId=1  And ProductId in (257,258) And (IsHedge=0 Or IsHedge=-2)),0) 
		+ (isnull((Select sum(saleprice) From Project Where CreateDate>=@sRq And CreateDate<=@eRq 
		And CompanyId =@companyId And NOE_Flag=0 And TypeId=1  And ProductId in (257,258) And IsHedge=-1),0))) 
		--新增毛利
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'XZML','新增毛利',0,@IAA)
		--续费毛利
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'XFML','续费毛利',0,@IAB)
		--实收纯利
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'SSCL','实收纯利',0,@sscl)
		--企业QQ
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'QYQQ','企业QQ',@qyQQ,@qyQQ_je)
		--百度
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'BD','百度',@bd,@bd_je)
		--百度直通车
		Insert into [Statistics_Performance]([StatisticsDate],[CompanyId],[CompanyName],[CategoryCode],[CategoryName],[TotalQuantity],[TotalAmount])
		Values(@date,@companyId,@companyName,'BDZTC','百度直通车',@bdztc,@bdztc_je)

		fetch next from cur_company into @companyId,@companyName
	End
END
